package kr.hkit.BusinessManagement.DBCon;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import kr.hkit.BusinessManagement.DTO.ProductDTO;
import kr.hkit.framework.dao.CloseDAOUtil;
import kr.hkit.framework.dao.LoadPropertyDAO;

public class ProductDBCon {
	private PreparedStatement pstmt;
	private ResultSet rs;
	private Connection con;
	
	public ProductDBCon() {
		con = LoadPropertyDAO.getconConnection();
	}

	public void addProduct(ProductDTO product){
		String sql = "insert into product values(?,?,?,?);";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, product.getCode());
			pstmt.setString(2, product.getName());
			pstmt.setInt(3, product.getSellPrice());
			pstmt.setInt(4, product.getOfferPrice());
			pstmt.executeUpdate();
			
			JOptionPane.showMessageDialog(null, product.toString() + "\n 등록 완료");
			//코드 네임 셀 오퍼
		} catch (SQLException e) {

			e.printStackTrace();
		}finally{
			CloseDAOUtil.close(pstmt);
		}
	}
	
	public ArrayList<ProductDTO> selectAll(){
		ArrayList<ProductDTO> productLists = new ArrayList<>();
		String sql = "select code, name, sellprice, offerprice from product;";
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				String code = rs.getString(1);
				String name = rs.getString(2);
				int sellPrice = rs.getInt(3);
				int offerPrice = rs.getInt(4);
				productLists.add(new ProductDTO(code, name, sellPrice, offerPrice));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			CloseDAOUtil.close(rs, pstmt);
		}
		
		return productLists;
	}

	public ProductDTO searchProductByCode(String productCode) {
		String sql = "select code, name, sellprice, offerprice from product where code =?;";
		String code = null, name = null;
		int sellPrice = 0, offerPrice = 0;

		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, productCode);
			rs = pstmt.executeQuery();
			while(rs.next()){
				code = rs.getString(1);
				name = rs.getString(2);
				sellPrice = rs.getInt(3);
				offerPrice = rs.getInt(4);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			CloseDAOUtil.close(rs, pstmt);
		}
		return new ProductDTO(code, name, sellPrice, offerPrice);
		
	}

	public void deleteProduct(ProductDTO product) {
		String sql = "delete from product where code =?;";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, product.getCode());
			pstmt.execute();
			
			JOptionPane.showMessageDialog(null, product + "\n삭제완료");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			CloseDAOUtil.close(pstmt);
		}
	}
}
